package com.netcracker.training.automonic.dao.entities.oracle;

import static com.netcracker.training.automonic.dao.entities.oracle.SQLQueries.*;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.netcracker.training.automonic.dao.entities.Object;
import com.netcracker.training.automonic.dao.entities.Statistics;
import com.netcracker.training.automonic.dao.entities.StatsType;
import com.netcracker.training.automonic.dao.factories.oracle.OracleDAOFactory;

/**
 * Concrete implementation for Oracle Database Express Edition 11g Release 2.
 * 
 * @author Vadim Gidulyan
 * 
 * @see Statistics
 */

public class OracleStatistics implements Statistics // Javadoc
{

	public static final Logger LOGGER = Logger.getLogger(OracleSite.class);
	
	private static OracleDAOFactory daoFactory = new OracleDAOFactory();
	private static Connection con = getConnection();
	
	private static Connection getConnection()
	{
		if (con == null)
			con = daoFactory.createConnection();
		return con;
	}
	
	public static Statistics createStatistics(Object object, StatsType statsType)
	{
		Statistics statistics = null;
		
		try (Statement st = con.createStatement())
		{
			String sql = "SELECT SYSDATE FROM DUAL";
			
			ResultSet rs = st.executeQuery(sql);
			
			if (rs.next())
			{
				Date time = rs.getDate(1);
				statistics = new OracleStatistics(time, object, statsType);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(statistics);
		}
		
		return statistics;
	}
	
	public static Statistics selectStatistics(Date time, Object object,
			StatsType statsType)
	{
		Statistics statistics = null;
		
		try (Statement stmt = con.createStatement())
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String sql = String.format(SELECT_STATISTICS_BY_TIME_OBJECT_TYPE,
			                           sdf.format(time),
			                           object.getId(),
			                           statsType.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			if (rs.next())
			{
				statistics = new OracleStatistics(rs.getDate("time"),
				                                  object,
				                                  statsType);
				statistics.setValue(rs.getString("value"));
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				LOGGER.trace(statistics);
		}
		
		return statistics;
	}
	
	public static List<Statistics> selectStatisticsByTime(Date time)
	{
		List<Statistics> statistics = new LinkedList<Statistics>();
		
		try (Statement stmt = con.createStatement())
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
			String sql = String.format(SELECT_STATISTICS_BY_TIME,
			                           sdf.format(time));
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				StatsType statsType = 
						OracleStatsType.selectStatsTypeById(
								rs.getInt("statstype"));
				
				Statistics statistic = 
						new OracleStatistics(rs.getDate("time"),
						                     object,
						                     statsType);
				statistic.setValue(rs.getString("value"));
				statistics.add(statistic);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Statistics statistic : statistics)
					LOGGER.trace(statistic);
		}
		
		return statistics;
	}

	public static List<Statistics> selectStatisticsByObject(Object object)
	{
		List<Statistics> statistics = new LinkedList<Statistics>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_STATISTICS_BY_OBJECT_ID,
			                           object.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				StatsType statsType = 
						OracleStatsType.selectStatsTypeById(
								rs.getInt("statstype"));
				
				Statistics statistic = 
						new OracleStatistics(rs.getDate("time"),
						                     object,
						                     statsType);
				statistic.setValue(rs.getString("value"));
				statistics.add(statistic);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Statistics statistic : statistics)
					LOGGER.trace(statistic);
		}
		
		return statistics;
	}

	public static List<Statistics> selectStatisticsByType(StatsType statsType)
	{
		List<Statistics> statistics = new LinkedList<Statistics>();
		
		try (Statement stmt = con.createStatement())
		{
			String sql = String.format(SELECT_STATISTICS_BY_TYPE_ID,
			                           statsType.getId());
			
			ResultSet rs = stmt.executeQuery(sql);
			
			while (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				Statistics statistic = 
						new OracleStatistics(rs.getDate("time"),
						                     object,
						                     statsType);
				statistic.setValue(rs.getString("value"));
				statistics.add(statistic);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Statistics statistic : statistics)
					LOGGER.trace(statistic);
		}
		
		return statistics;
	}
	
	public static List<Statistics> selectAllStatistics()
	{
		List<Statistics> statistics = new LinkedList<Statistics>();
		
		try (Statement stmt = con.createStatement())
		{
			ResultSet rs = stmt.executeQuery(SELECT_ALL_ATTRIBUTES);
			
			while (rs.next())
			{
				Object object = 
						OracleObject.selectObjectById(rs.getInt("objectid"));
				
				StatsType statsType = 
						OracleStatsType.selectStatsTypeById(
								rs.getInt("statstype"));
				
				Statistics statistic = 
						new OracleStatistics(rs.getDate("time"),
						                     object,
						                     statsType);
				statistic.setValue(rs.getString("value"));
				statistics.add(statistic);
			}
			rs.close();
		}
		catch (SQLException e)
		{
			LOGGER.error(null, e);
			e.printStackTrace();
		}
		finally
		{
			if (LOGGER.isTraceEnabled())
				for (Statistics statistic : statistics)
					LOGGER.trace(statistic);
		}
		
		return statistics;
	}
	
	private final Date time;
	private final Object object;
	private final StatsType statsType;
	private String value = "";
	
	private boolean modified;
	
	// CONSTRUCTORS
	
	protected OracleStatistics(Date time, Object object, StatsType statsType)
	{
		this.time      = time;
		this.object    = object;
		this.statsType = statsType;
		this.value     = "";
		
		modified = false;
	}
	
	// ACCESSORS
	
	@Override
	public Date getTime()
	{
		return time;
	}

	@Override
	public Object getObject()
	{
		return object;
	}

	@Override
	public StatsType getType()
	{
		return statsType;
	}

	@Override
	public String getValue()
	{
		return value;
	}

	@Override
	public void setValue(String value)
	{
		if (this.value.hashCode() != value.hashCode())
			modified = true;
		this.value = value;
	}
	
	// METHODS
	
	@Override
	public String toString()
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
		return String.format("%s [%s] %s : %s",
		                     sdf.format(time), 
		                     object.getName(), 
		                     value, 
		                     statsType.getCode());
	}
	
	@Override
	public void save()
	{
		boolean statisticsExists = 
				(selectStatistics(time, object, statsType) != null);
		
		if (statisticsExists)
		{
			if (modified)
			{
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
				String sql = String.format(
						"UPDATE Statistics "
					  + "SET value = '%s' "
					  + "WHERE "
					  + "	TRUNC(time, 'MI') = TO_DATE('%s', 'YYYY-MM-DD HH24:MI') "
					  + "	AND objectid = %d "
					  + "	AND statstype = %d",
						value, sdf.format(time), object.getId(), statsType.getId());
				
				try (Statement st = con.createStatement())
				{
					st.executeUpdate(sql);
				}
				catch (SQLException e)
				{
					LOGGER.error(null, e);
					e.printStackTrace();
				}
				finally
				{
					if (LOGGER.isTraceEnabled())
						LOGGER.trace(sql);
				}
			}
		}
		else
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String sql = String.format(
					"INSERT INTO Statistics "
				  + "	VALUES (TO_DATE('%s', 'YYYY-MM-DD HH24:MI:SS'), "
				  + "	        %d, %d, '%s')",
					sdf.format(time), object.getId(), statsType.getId(), value);
			
			try (Statement st = con.createStatement())
			{
				st.executeUpdate(sql);
			}
			catch (SQLException e)
			{
				LOGGER.error(null, e);
				e.printStackTrace();
			}
			finally
			{
				if (LOGGER.isTraceEnabled())
					LOGGER.trace(sql);
			}
		}
		
		modified = false;
		LOGGER.info("Saved statistics: " + this);
	}

}
